-- Synchronize the Member Type Security Groups with Member_Types
CREATE PROCEDURE [dbo].[asi_SyncMemberTypeSecurityGroups]
AS
BEGIN
    SET NOCOUNT ON
    -- Declare all variables needed
    DECLARE @now datetime
    DECLARE @userKey uniqueidentifier
    DECLARE @accessKey uniqueidentifier
    DECLARE @groupTypeKey uniqueidentifier
    DECLARE @systemEntityKey uniqueidentifier
    DECLARE @groupComponentKey uniqueidentifier
    DECLARE @groups TABLE ([GroupKey] uniqueidentifier, [Name] nvarchar(65), [Description] nvarchar(250))

    -- Grab some constant values
    SELECT @now = GETDATE()
    SELECT @userKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = 'SYSTEM'
    IF @userKey IS NULL SELECT @userKey = [UserKey] FROM [dbo].[UserMain] WHERE [UserId] = 'MANAGER'
    SELECT @accessKey = [ProtectedAccessKey] FROM [dbo].[AccessArea] WHERE [Name] = 'Everyone Full Control'
    SET @groupTypeKey = '636A1E55-38BC-4A8F-AC8D-A6873D697F18'
    SELECT @systemEntityKey = [SystemEntityKey] FROM [dbo].[SystemEntity] WHERE [SystemKeyword] = 'Organization'
    SELECT @groupComponentKey = [ComponentKey] FROM [dbo].[ComponentRegistry] WHERE [Name] = 'Group' AND [InterfaceName] = 'BusinessController'

    -- Create Group Type if it doesn't already exist
    IF NOT EXISTS (SELECT 1 FROM [dbo].[GroupTypeRef] WHERE [GroupTypeName] = 'Member Type Security')
    BEGIN
        INSERT INTO [dbo].[GroupTypeRef] ([GroupTypeKey], [GroupTypeName], [IsSystem], [IsPaymentRequired], [IsDateLimited],
                                          [GroupMemberBranchName], [IsInvitationOnly], [DefaultGroupStatusCode], [IsSimpleGroup],
                                          [MemberQueryFolderKey], [InheritRolesFlag], [IsSingleRole], [GroupTypeDesc],
                                          [CreatedByUserKey], [UpdatedByUserKey], [CreatedOn], [UpdatedOn])
        VALUES (@groupTypeKey, 'Member Type Security', 1, 0, 0,
                NULL, 0, 'A', 1, NULL, 0, 0, 'Member Type Security Group Type',
                @userKey, @userKey, @now, @now)
    END

    -- Get a list of all the groups we should have, one for each member type
    INSERT INTO @groups ([GroupKey], [Name], [Description])
        SELECT COALESCE(gm.GroupKey, NEWID()), mt.[DESCRIPTION], 'Security Group for ' + mt.[DESCRIPTION] + ' users'
          FROM [dbo].[Member_Types] mt LEFT OUTER JOIN [dbo].[GroupMain] gm ON gm.[Name] = mt.[DESCRIPTION]

    -- Remove any Members of Member Type Security groups that don't have a corresponding member type in Member_Types
    DELETE m
      FROM [dbo].[GroupMember] m
           INNER JOIN [dbo].[GroupMain] gm ON m.GroupKey = gm.[GroupKey]
           LEFT OUTER JOIN @groups g ON gm.[Name] = g.[Name]
     WHERE gm.[GroupTypeKey] = @groupTypeKey
       AND g.[Name] IS NULL

    -- Remove any Member Type Security groups that don't have a corresponding member type in Member_Types
    DELETE gm
      FROM [dbo].[GroupMain] gm
           LEFT OUTER JOIN @groups g ON gm.[Name] = g.[Name]
           INNER JOIN [dbo].[UniformRegistry] ON gm.[GroupKey] = gm.[GroupKey]
     WHERE gm.[GroupTypeKey] = @groupTypeKey
       AND g.[Name] IS NULL

    -- Remove Uniform Registry entries for the deleted groups
    DELETE ur
      FROM [dbo].[UniformRegistry] ur
           LEFT OUTER JOIN [dbo].[GroupMain] gm ON ur.[UniformKey] = gm.[GroupKey]
     WHERE ur.[ComponentKey] = @groupComponentKey AND gm.[GroupKey] IS NULL

    -- Create Uniform Registry entry for each new security group
    INSERT INTO [dbo].[UniformRegistry] ([UniformKey], [ComponentKey])
        SELECT g.[GroupKey], @groupComponentKey
          FROM @groups g LEFT OUTER JOIN [dbo].[UniformRegistry] ur ON ur.[UniformKey] = g.[GroupKey] AND ur.[ComponentKey] = @groupComponentKey
         WHERE  ur.[UniformKey] IS NULL

    -- Create a new Member Type Security group for each member type in Member_Types
    INSERT INTO [dbo].[GroupMain] ([GroupKey], [Name], [Description], [UpdatedByUserKey], [UpdatedOn], [IsSystem], [IsAutoGenerated],
                                   [GroupTypeKey], [Priority], [OwnerAccessKey], [OverrideOwnerGroupKey], [AccessKey],
                                   [CreatedByUserKey], [CreatedOn], [SystemEntityKey],
                                   [IsInvitationOnly], [GroupStatusCode], [IsSimpleGroup], [InheritRolesFlag], [IsSingleRole])
        SELECT g.[GroupKey], g.[Name], g.[Description], @userKey, @now, 1, 1,
               @groupTypeKey, NULL, NULL, NULL, @accessKey, @userKey, @now, @systemEntityKey, 0, 'A', 1, 0, 0
          FROM @groups g
         WHERE NOT EXISTS (SELECT 1 FROM [dbo].[GroupMain] gm WHERE g.[Name] = gm.[Name] AND gm.[GroupTypeKey] = @groupTypeKey)

    -- Populate the new Security Groups
    INSERT INTO [dbo].[GroupMember] ([GroupMemberKey], [GroupKey], [MemberContactKey], [IsActive],
                                     [CreatedByUserKey], [CreatedOn], [UpdatedByUserKey], [UpdatedOn],
                                     [DropDate], [JoinDate], [MarkedForDeleteOn])
        SELECT NEWID(), g.GroupKey, cm.ContactKey, 1, @userKey, @now, @userKey, @now, NULL, NULL, NULL  
          FROM [dbo].[Name] n INNER JOIN [Member_Types] mt ON n.MEMBER_TYPE = mt.[MEMBER_TYPE]
               INNER JOIN [dbo].[ContactMain] cm ON n.[ID] = cm.[SyncContactID]
               INNER JOIN [dbo].[UserMain] um ON um.UserKey = cm.ContactKey
               INNER JOIN [dbo].[GroupMain] g ON g.[Name] = mt.[DESCRIPTION]
         WHERE NOT EXISTS (SELECT 1 FROM [dbo].[GroupMember] gm WHERE gm.GroupKey = g.GroupKey AND gm.MemberContactKey = um.UserKey)
    SET NOCOUNT OFF
END

GO